///////////////////////////////////////////////////////////////////////////////
//
// Licensed under the MIT License
//
// Copyright (c) 2010 Chris Colinsky
// All Rights Reserved
//
// version	0.5
// http://code.google.com/p/sugarcookie/
// http://code.google.com/p/sugarcookie/wiki/License
//
////////////////////////////////////////////////////////////////////////////////

package sugarcookie.data.database {
	import flash.events.EventDispatcher;
	import flash.events.IEventDispatcher;
	
	import mx.utils.ObjectUtil;

	public class Database extends EventDispatcher {
		private var _data:Array = [];
		
		public function Database() {
			super();
		}
		
		public function createTable(name:String, columns:Array):Boolean {
			if (doesKeyExist(name,_data)) return false;
			
			_data[name] = [];
			_data[name].push(columns);
			
			return true;
		}
		
		public function insertInto(table:String, values:Array):Boolean {
			if (!doesKeyExist(table,_data)) return false;
			
			_data[table].push(values);
			
			return true;
		}
		
		public function empty(table:String=null):Boolean {
			if (!doesKeyExist(table,_data)) return false;
			
			if (table!=null) _data[table] = [];
			else _data = [];
			
			return true;
		}
		
		// SELECT column+ IN table-name WHERE ccolumn+ condition+ condition=='MATCH'?'value'+ : 'value'
		public function selectWhere(table:String, return_columns:Array, conditions:Array=null):Array {
			var fullResults:Array = [];
			var col_ids:Array;
			var record:Array;
			
			for (var i:int=1;i<_data[table].length;i++) { //loop through table to find matching results
				//create record
				record = _data[table][i];
				
				if (conditions!=null && conditions.length>0) {
				//var ismatch:Boolean = false;
					var conditions_matched:int = 0;
					var cols_matched:int;
					var n:int;
					for (var x:int=0;x<conditions.length;x++) { //loop through conditions to find matches for each record
						cols_matched = 0;
						col_ids = getColumnIds(table,conditions[x].column);
						
						if (conditions[x].condition=="EQUALS") {
							for (n=0;n<col_ids.length;n++) {
								if (record[col_ids[n]]==conditions[x].value) cols_matched++;
							}
						} // end equals
						else if (conditions[x].condition=="MATCH") {
							for (n=0;n<col_ids.length;n++) {
								if (typeof conditions[x].value == "string") {
									if (String(record[col_ids[n]]).toLowerCase().search( String(conditions[x].value).toLowerCase() ) != -1) cols_matched++;
								} else if (typeof conditions[x].value == "object") {
									var values:Array = conditions[x].value as Array;
									var count:int = 0;
									for (var w:int=0;w<values.length;w++) {
										if (String(record[col_ids[n]]).toLowerCase().search( String(values[w]).toLowerCase() ) != -1) count++;
									}
									if (count>0) {
										record["matches"] = count;
										cols_matched++;
									}
								}
							}
						} // end match
						
						if (cols_matched>0) conditions_matched++;
					}
					if (conditions_matched==conditions.length) fullResults.push(record);
				} else { // there are no conditions
					fullResults.push(record);
				}
			}
			
			//pair down result to only requested columns
			var results:Array = [];
			col_ids = getColumnIds(table,return_columns);
			
			for (n=0;n<fullResults.length;n++) {
				record = [];
				for (var z:int=0;z<col_ids.length;z++) {
					record[_data[table][0][col_ids[z]]] = fullResults[n][col_ids[z]];
					
					if ( fullResults[n]["matches"] ) record["matches"] = fullResults[n]["matches"];
					else record["matches"] = 1;
				}
				results.push(record);
			}
			results.sortOn("matches", Array.NUMERIC | Array.DESCENDING);
			
			return results;
		}
		
		// SELECT DISTINCT column+ IN table-name
		public function selectDistinct(table:String, return_columns:Array):Array {
			//get column ids
			var col_ids:Array = getColumnIds(table,return_columns);
			var fullResults:Array = [];
			for (var i:int=1;i<_data[table].length;i++) { //loop through rows in table
				//create record
				var record:Array = _data[table][i];
				
				var ismatch:Boolean = false;
				for (var x:int=0;x<fullResults.length;x++) { //compare current record values for columns requested against those aready in the result set
					var matched:int = 0;
					for (var g:int=0;g<col_ids.length;g++) {
						if (record[col_ids[g]] == fullResults[x][col_ids[g]]) matched++;
					}
					if (matched==col_ids.length) ismatch = true;
				}
				if (!ismatch) fullResults.push(record);
			}
			
			//pair down result to only requested columns
			var results:Array = [];
			for (var n:int=0;n<fullResults.length;n++) {
				results[n] = [];
				for (var z:int=0;z<col_ids.length;z++) {
					results[n][return_columns[z]] = fullResults[n][col_ids[z]];
				}
			}
			
			return results;
		}
		
		override public function toString():String {
			return ObjectUtil.toString(_data);
		}
		
		private function getColumnIds(table:String, columns:Array):Array {
			var col_ids:Array = [];
			var row_names:Array = _data[table][0];
			for (var i:int=0;i<columns.length;i++) {
				for (var n:int=0;n<row_names.length;n++) {
					if (row_names[n]==columns[i]) {
						col_ids.push(n);
					} 
					else if (columns[i]=="*") {
						col_ids.push(n);
					}
				}
			}
			return col_ids;
		}
		
		private function doesKeyExist(key:String,arr:Array):Boolean {
			var status:Boolean = false;
			for (var i:String in arr) {
				if (i==key) {
					status = true;
					break;
				}
			}
			return status;
		}
	}
}